package com.luych.toolbox.data.service.core.mysql;

import com.luych.toolbox.common.enums.DataSourceType;
import com.luych.toolbox.data.service.core.CoreConnection;
import com.luych.toolbox.data.service.core.CoreDatabase;
import com.mysql.cj.jdbc.Driver;
import com.mysql.cj.jdbc.MysqlDataSource;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.jdbc.core.JdbcTemplate;

import javax.sql.DataSource;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Objects;

public class MysqlConnection implements CoreConnection {

    private static final Logger LOGGER = LoggerFactory.getLogger(MysqlConnection.class);

    private final String db;

    private final DataSource dataSource;
    private final JdbcTemplate jdbcTemplate;

    public MysqlConnection(String host, int port, String user, String pass, String db) {
        String url = String.format("jdbc:mysql://%s:%d/%s?allowMultiQueries=true", host, port, db);
        this.dataSource = DataSourceBuilder.create()
                .type(MysqlDataSource.class)
                .driverClassName(Driver.class.getName())
                .url(url).username(user).password(pass)
                .build();
        this.jdbcTemplate = new JdbcTemplate(dataSource);
        this.db = db;
    }

    private List<MysqlTable> getTables(String database) {
        LOGGER.info("Get [" + database + "] Tables ...");
        List<Map<String, Object>> tableMaps = jdbcTemplate.queryForList(
                "SELECT TABLE_NAME, TABLE_COMMENT " +
                "FROM information_schema.TABLES " +
                "WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_SCHEMA = '" + database + "' "
        );
        LOGGER.info("Get [" + database + "] Columns ...");
        List<Map<String, Object>> columnMaps = jdbcTemplate.queryForList(
                "SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, COLUMN_TYPE, IS_NULLABLE, COLUMN_COMMENT, " +
                "       CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE, EXTRA " +
                "FROM information_schema.COLUMNS " +
                "WHERE TABLE_SCHEMA = '" + database + "' "
        );
        LOGGER.info("Get [" + database + "] PrimaryKeys ...");
        List<Map<String, Object>> primaryKeyMaps = jdbcTemplate.queryForList(
                "SELECT TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION, CONSTRAINT_NAME " +
                "FROM information_schema.KEY_COLUMN_USAGE " +
                "WHERE TABLE_SCHEMA = '" + database + "' AND CONSTRAINT_NAME = 'PRIMARY' " +
                "ORDER BY TABLE_NAME, ORDINAL_POSITION"
        );

        List<MysqlTable> tables = new ArrayList<>();
        for (Map<String, Object> tableMap : tableMaps) {
            String tableName = Objects.toString(tableMap.get("TABLE_NAME"), "");
            String tableComment = Objects.toString(tableMap.get("TABLE_COMMENT"), "");
            List<MysqlColumn> columns = new ArrayList<>();
            for (Map<String, Object> columnMap : columnMaps) {
                if (tableName.equals(Objects.toString(columnMap.get("TABLE_NAME"), ""))) {
                    String columnName = Objects.toString(columnMap.get("COLUMN_NAME"), "");
                    String columnDataType = Objects.toString(columnMap.get("DATA_TYPE"), "");
                    String columnColumnType = Objects.toString(columnMap.get("COLUMN_TYPE"), "");
                    String columnNullable = Objects.toString(columnMap.get("IS_NULLABLE"), "");
                    String columnComment = Objects.toString(columnMap.get("COLUMN_COMMENT"), "");
                    String columnCharLength = Objects.toString(columnMap.get("CHARACTER_MAXIMUM_LENGTH"), null);
                    String columnNumericPrecision = Objects.toString(columnMap.get("NUMERIC_PRECISION"), null);
                    String columnNumericScale = Objects.toString(columnMap.get("NUMERIC_SCALE"), null);
                    String columnIncrement = Objects.toString(columnMap.get("EXTRA"), "");

                    MysqlDataType mysqlDataType = MysqlDataType.valueOf(columnDataType.toUpperCase());

                    if (MysqlDataType.TINYINT.equals(mysqlDataType)) {
                        if (columnColumnType.contains("(") && columnColumnType.contains(")")) {
                            columnNumericPrecision = columnColumnType
                                    .replace("tinyint", "").replace("TINYINT", "")
                                    .replace("unsigned", "").replace("UNSIGNED", "")
                                    .replace("zerofill", "").replace("ZEROFILL", "")
                                    .replace("(", "").replace(")", "").trim();
                        } else {
                            columnNumericPrecision = null;
                        }
                    }

                    MysqlColumn column = new MysqlColumn(
                            tableName, columnName, mysqlDataType,
                            columnCharLength == null ? null : Long.parseLong(columnCharLength),
                            columnNumericPrecision == null ? null : Long.parseLong(columnNumericPrecision),
                            columnNumericScale == null ? null : Long.parseLong(columnNumericScale),
                            "YES".equals(columnNullable),
                            columnComment,
                            "auto_increment".equals(columnIncrement)
                    );
                    columns.add(column);
                }
            }
            String primaryKeyName = null;
            List<String> primaryKeys = new ArrayList<>();
            for (Map<String, Object> primaryKeyMap : primaryKeyMaps) {
                if (tableName.equals(Objects.toString(primaryKeyMap.get("TABLE_NAME"), ""))) {
                    primaryKeys.add(Objects.toString(primaryKeyMap.get("COLUMN_NAME"), ""));
                    if (primaryKeyName == null && primaryKeyMap.containsKey("CONSTRAINT_NAME")) {
                        primaryKeyName = Objects.toString(primaryKeyMap.get("CONSTRAINT_NAME"));
                    }
                }
            }
            MysqlTable table = new MysqlTable(tableName, tableComment, columns, primaryKeyName, primaryKeys);
            tables.add(table);
        }
        return tables;
    }

    private List<MysqlView> getViews(String database) {
        LOGGER.info("Get [" + database + "] Views ...");
        List<Map<String, Object>> viewMaps = jdbcTemplate.queryForList(
                "SELECT TABLE_NAME FROM information_schema.TABLES " +
                        "WHERE TABLE_TYPE = 'VIEW' AND TABLE_SCHEMA = '" + database + "' "
        );
        List<MysqlView> views = new ArrayList<>();
        for (Map<String, Object> viewMap : viewMaps) {
            String viewName = Objects.toString(viewMap.get("TABLE_NAME"), "");
            views.add(new MysqlView(viewName));
        }
        return views;
    }

    @Override
    public DataSourceType getDataSourceType() {
        return DataSourceType.MYSQL;
    }

    @Override
    public CoreDatabase getDatabase() {
        return new MysqlDatabase(db, this.getTables(db), this.getViews(db));
    }

    @Override
    public DataSource getDataSource() {
        return this.dataSource;
    }
}
